# -*- coding: utf-8 -*-
"""
数据库操作模块
负责VideoInfo数据的存储、查询和管理
"""
import json
import sqlite3
from pathlib import Path
from typing import List, Optional, Tuple

from .models import VideoInfo, VideoType, VideoSubType


class VideoDatabase:
    """视频数据库操作类"""

    def __init__(self, db_path: str = "duplicate_checker.db"):
        """
        初始化数据库连接
        
        :param db_path: 数据库文件路径
        """
        self.db_path = Path(db_path)
        self.connection = None
        self._ensure_database_exists()
        self._create_tables()

    def _ensure_database_exists(self):
        """确保数据库文件存在"""
        self.db_path.parent.mkdir(parents=True, exist_ok=True)

    def _get_connection(self) -> sqlite3.Connection:
        """获取数据库连接"""
        if self.connection is None:
            self.connection = sqlite3.connect(str(self.db_path))
            self.connection.row_factory = sqlite3.Row
        return self.connection

    def _create_tables(self):
        """创建数据库表结构"""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
                       CREATE TABLE IF NOT EXISTS video_info
                       (
                           id
                           INTEGER
                           PRIMARY
                           KEY
                           AUTOINCREMENT,
                           original_filename
                           TEXT
                           NOT
                           NULL,
                           clean_core_id
                           TEXT
                           NOT
                           NULL
                           UNIQUE,
                           video_type
                           TEXT
                           NOT
                           NULL,
                           video_subtype
                           TEXT,
                           prefix
                           TEXT,
                           number
                           TEXT,
                           extension
                           TEXT
                           NOT
                           NULL,
                           has_subtitle
                           BOOLEAN
                           NOT
                           NULL
                           DEFAULT
                           0,
                           has_leak
                           BOOLEAN
                           NOT
                           NULL
                           DEFAULT
                           0,
                           has_4k
                           BOOLEAN
                           NOT
                           NULL
                           DEFAULT
                           0,
                           segment_count
                           INTEGER
                           NOT
                           NULL
                           DEFAULT
                           1,
                           suffix_parts
                           TEXT,
                           is_dirty
                           BOOLEAN
                           NOT
                           NULL
                           DEFAULT
                           0,
                           error_reason
                           TEXT,
                           created_at
                           TIMESTAMP
                           DEFAULT
                           CURRENT_TIMESTAMP,
                           updated_at
                           TIMESTAMP
                           DEFAULT
                           CURRENT_TIMESTAMP
                       )
                       """)

        cursor.execute("CREATE INDEX IF NOT EXISTS idx_clean_core_id ON video_info(clean_core_id)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_video_type ON video_info(video_type)")
        conn.commit()

    def insert_video(self, video_info: VideoInfo) -> bool:
        """插入“虚拟”视频信息到数据库"""
        conn = self._get_connection()
        cursor = conn.cursor()

        try:
            cursor.execute("""
                           INSERT INTO video_info (original_filename, clean_core_id, video_type, video_subtype,
                                                   prefix, number, extension, has_subtitle, has_leak, has_4k,
                                                   segment_count, suffix_parts, is_dirty, error_reason)
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                           """, (
                               video_info.original_filename, video_info.clean_core_id,
                               video_info.video_type.value,
                               video_info.video_subtype.value if video_info.video_subtype else None,
                               video_info.prefix, video_info.number, video_info.extension,
                               video_info.has_subtitle, video_info.has_leak, video_info.has_4k,
                               video_info.segment_count,
                               json.dumps(video_info.suffix_parts, ensure_ascii=False),
                               video_info.is_dirty, video_info.error_reason
                           ))
            conn.commit()
            return True
        except sqlite3.IntegrityError:
            return False
        except Exception as e:
            conn.rollback()
            raise e

    def get_by_core_id(self, clean_core_id: str) -> Optional[VideoInfo]:
        """根据clean_core_id获取视频信息"""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM video_info WHERE clean_core_id = ?", (clean_core_id,))
        row = cursor.fetchone()

        if row:
            return self._row_to_video_info(row)
        return None

    def batch_insert_videos(self, video_infos: List[VideoInfo]) -> Tuple[int, List[str]]:
        """批量插入“虚拟”视频信息"""
        conn = self._get_connection()
        cursor = conn.cursor()

        success_count = 0
        failed_ids = []

        for video_info in video_infos:
            cursor.execute("""
                           INSERT INTO video_info (original_filename, clean_core_id, video_type, video_subtype,
                                                   prefix, number, extension, has_subtitle, has_leak, has_4k,
                                                   segment_count, suffix_parts, is_dirty, error_reason)
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                           """, (
                               video_info.original_filename, video_info.clean_core_id,
                               video_info.video_type.value,
                               video_info.video_subtype.value if video_info.video_subtype else None,
                               video_info.prefix, video_info.number, video_info.extension,
                               video_info.has_subtitle, video_info.has_leak, video_info.has_4k,
                               video_info.segment_count,
                               json.dumps(video_info.suffix_parts, ensure_ascii=False),
                               video_info.is_dirty, video_info.error_reason
                           ))
            success_count += 1

        conn.commit()
        return success_count, failed_ids

    def get_numbers_by_prefix(self, prefix: str) -> List[int]:
        """查询指定前缀的所有数字序号"""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("""
                       SELECT number
                       FROM video_info
                       WHERE prefix = ?
                         AND number IS NOT NULL
                         AND is_dirty = 0
                       ORDER BY CAST(number AS INTEGER)
                       """, (prefix.upper(),))

        results = [int(row['number']) for row in cursor.fetchall() if row['number'] and row['number'].isdigit()]
        return results

    def get_statistics(self) -> dict:
        """获取数据库统计信息"""
        conn = self._get_connection()
        cursor = conn.cursor()

        cursor.execute("SELECT COUNT(*) as total FROM video_info")
        total = cursor.fetchone()['total']

        cursor.execute("SELECT video_type, COUNT(*) as count FROM video_info WHERE is_dirty = 0 GROUP BY video_type")
        type_stats = {row['video_type']: row['count'] for row in cursor.fetchall()}

        cursor.execute("SELECT COUNT(*) as dirty_count FROM video_info WHERE is_dirty = 1")
        dirty_count = cursor.fetchone()['dirty_count']

        cursor.execute("SELECT MAX(created_at) as latest FROM video_info")
        latest = cursor.fetchone()['latest']

        return {
            'total_records': total,
            'type_distribution': type_stats,
            'dirty_records': dirty_count,
            'clean_records': total - dirty_count,
            'latest_added': latest
        }

    def _row_to_video_info(self, row: sqlite3.Row) -> VideoInfo:
        """将数据库行转换为VideoInfo对象"""
        return VideoInfo(
            original_filename=row['original_filename'],
            video_type=VideoType(row['video_type']),
            core_id=row['clean_core_id'],
            prefix=row['prefix'],
            number=row['number'],
            extension=row['extension'],
            has_subtitle=bool(row['has_subtitle']),
            has_leak=bool(row['has_leak']),
            has_4k=bool(row['has_4k']),
            segment_count=row['segment_count'],
            video_subtype=VideoSubType(row['video_subtype']) if row['video_subtype'] else None,
            suffix_parts=json.loads(row['suffix_parts']) if row['suffix_parts'] else [],
            is_dirty=bool(row['is_dirty']),
            error_reason=row['error_reason']
        )

    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()
